# -*- coding: utf-8 -*-
# @Time : 2025/2/24 15:24
# @Author : debbyszhang
# @File : mysql_opr.py
# @desc :


from mysql.connector import Error
import mysql.connector
from mysql.connector import pooling

import mysql.connector
from mysql.connector import Error


class MySQLHelper:
    def __init__(self, host, user, password, port, database):
        """
        初始化数据库连接
        :param host: 数据库主机地址
        :param user: 数据库用户名
        :param password: 数据库密码
        :param database: 数据库名称
        """
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.connection = None
        self.port = port

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port
            )
            if self.connection.is_connected():
                print("数据库连接成功")
        except Error as e:
            print(f"数据库连接失败: {e}")
            self.connection = None

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")

    def execute_query(self, query, params=None):
        """
        执行查询操作
        :param query: SQL查询语句
        :param params: 查询参数（可选）
        :return: 查询结果
        """
        try:
            cursor = self.connection.cursor(dictionary=True)
            cursor.execute(query, params)
            result = cursor.fetchall()
            return result
        except Error as e:
            print(f"查询失败: {e}")
            return None

    def execute_command(self, command, params=None):
        """
        执行添加、修改、删除操作
        :param command: SQL命令
        :param params: 命令参数（可选）
        :return: 影响的行数
        """
        try:
            cursor = self.connection.cursor()
            cursor.execute(command, params)
            self.connection.commit()
            return cursor.rowcount
        except Error as e:
            print(f"操作失败: {e}")
            return -1


# 示例用法
if __name__ == "__main__":
    # 配置数据库连接信息
    db = MySQLHelper(
        host="192.168.0.173",
        user="root",
        password="password",
        port=13306,
        database="tcip_mgr"
    )

    # 连接数据库
    db.connect()

    # 更新数据
    # update_data = {"subchain_count": 0}
    # db.update("cross_gateway", update_data, "gateway_name = 'tcip-chainmaker20250125140027'")
    #
    # # 删除数据
    # db.delete("sub_chain", "chain_rid = 'chainmaker00102'")

    # # 查询操作
    # query = "SELECT * FROM your_table WHERE id = %s"
    # params = (1,)
    # result = db.execute_query(query, params)
    # print(result)
    #
    # # 添加操作
    # insert_query = "INSERT INTO your_table (name, age) VALUES (%s, %s)"
    # insert_params = ("John", 30)
    # rows_affected = db.execute_command(insert_query, insert_params)
    # print(f"添加了 {rows_affected} 行")

    # 修改操作
    update_query = "UPDATE cross_gateway SET subchain_count = %s WHERE gateway_name = %s"
    update_params = (0, 'tcip-chainmaker20250125140027')
    rows_affected = db.execute_command(update_query, update_params)
    print(f"修改了 {rows_affected} 行")

    # 删除操作
    delete_query = "DELETE FROM sub_chain WHERE chain_rid = %s"
    delete_params = ('chainmaker00102',)
    rows_affected = db.execute_command(delete_query, delete_params)
    print(f"删除了 {rows_affected} 行")

    if db.connection:
        result = db.execute_query("SELECT VERSION()")
        if result:
            print("MySQL版本:", result[0]['VERSION()'])
        else:
            print("查询失败")
    else:
        print("无法连接到数据库")

    db.disconnect()

    """
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

FLUSH PRIVILEGES;
    """

    """
    DELETE FROM sub_chain WHERE chain_rid = 'chainmaker00102';

UPDATE cross_gateway
SET subchain_count = 0
WHERE gateway_name = 'tcip-chainmaker20250125140027';

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.


    """
